#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库迁移脚本
用于将旧的Sample数据结构迁移到新的数据结构
"""

import sqlite3
import json
import logging
from datetime import datetime
from pathlib import Path
from typing import List, Dict, Any

logger = logging.getLogger(__name__)

class DatabaseMigration:
    """数据库迁移工具"""
    
    def __init__(self, db_path: str):
        self.db_path = db_path
        self.backup_path = db_path.replace('.db', '_backup.db')
    
    def create_backup(self) -> bool:
        """创建数据库备份"""
        try:
            import shutil
            shutil.copy2(self.db_path, self.backup_path)
            logger.info(f"数据库备份已创建: {self.backup_path}")
            return True
        except Exception as e:
            logger.error(f"创建数据库备份失败: {e}")
            return False
    
    def migrate_samples_table(self) -> bool:
        """迁移samples表结构"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 1. 创建新的samples表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS samples_new (
                        id TEXT PRIMARY KEY,
                        protocol TEXT DEFAULT 'CUSTOM_LIS',
                        machine_model TEXT,
                        machine_id TEXT,
                        
                        -- 子对象字段，使用JSON存储
                        sample_info TEXT,  -- JSON格式存储SampleInfo
                        results TEXT,      -- JSON格式存储Results
                        image TEXT,        -- JSON格式存储ImageInfo
                        analysis_mode TEXT,
                        mode_info TEXT,    -- JSON格式存储ModeInfo
                        
                        -- 原始数据和接收时间
                        raw TEXT,          -- 原始数据
                        receive_time TIMESTAMP,
                        
                        -- 状态和时间戳
                        status TEXT DEFAULT 'PENDING',
                        created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                ''')
                
                # 2. 创建图片数据表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS sample_images (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        sample_id TEXT NOT NULL,
                        filename TEXT,
                        hex_data TEXT,     -- 十六进制数据
                        base64_data TEXT,  -- Base64数据
                        file_size INTEGER,
                        created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        FOREIGN KEY (sample_id) REFERENCES samples_new (id) ON DELETE CASCADE
                    )
                ''')
                
                # 3. 迁移现有数据
                cursor.execute('SELECT * FROM samples')
                old_samples = cursor.fetchall()
                
                logger.info(f"开始迁移 {len(old_samples)} 个样本")
                
                for i, old_sample in enumerate(old_samples):
                    try:
                        # 解析旧数据结构
                        old_id = old_sample[0]
                        old_instrument_id = old_sample[1]
                        old_instrument_model = old_sample[2]
                        old_instrument_number = old_sample[3]
                        old_protocol_type = old_sample[4]
                        old_communication_config = old_sample[5]
                        old_sample_number = old_sample[6]
                        old_sample_id = old_sample[7]
                        old_instrument_type = old_sample[8]
                        old_analysis_time = old_sample[9]
                        old_patient_name = old_sample[10]
                        old_patient_age = old_sample[11]
                        old_patient_gender = old_sample[12]
                        old_test_image_path = old_sample[13]
                        old_test_results = old_sample[14]
                        old_report_id = old_sample[15]
                        old_status = old_sample[16]
                        old_created_time = old_sample[17]
                        old_updated_time = old_sample[18]
                        
                        # 创建新的sample_info JSON
                        sample_info = {
                            'sample_id': old_sample_id or '',
                            'sample_number': old_sample_number or '',
                            'patient_name': old_patient_name or '',
                            'patient_age': old_patient_age or 0,
                            'patient_gender': old_patient_gender or '',
                            'datetime': old_analysis_time or '',
                            'sample_type': '',
                            'sample_type_name': '',
                            'sample_category': '',
                            'report_id': old_report_id or ''
                        }
                        
                        # 创建新的results JSON
                        results = {
                            'HbA1a': '',
                            'HbA1b': '',
                            'HbF': '',
                            'LA1c': '',  # 修正字段名
                            'HbA1c': '',
                            'HbA0': '',
                            'eAG': '',
                            'A2': '',
                            'HbE': '',
                            'HbD': '',
                            'HbS': '',
                            'HbC': '',
                            'p3': '',
                            'v_win': '',
                            'TIME': [],
                            'AREA': [],
                            'TIME_DETAILS': {},
                            'AREA_DETAILS': {},
                            'other_results': {}
                        }
                        
                        # 尝试从test_results中提取数据
                        if old_test_results:
                            try:
                                # 简单的解析逻辑，可以根据实际数据格式调整
                                lines = old_test_results.split('\n')
                                for line in lines:
                                    if ':' in line:
                                        key, value = line.split(':', 1)
                                        key = key.strip()
                                        value = value.strip()
                                        
                                        # 映射到新的字段
                                        if key in results:
                                            results[key] = value
                                        else:
                                            results['other_results'][key] = value
                            except Exception as e:
                                logger.warning(f"解析test_results失败: {e}")
                        
                        # 创建新的image JSON
                        image = {
                            'filename': '',
                            'size': 0,
                            'path': old_test_image_path or '',
                            'saved': bool(old_test_image_path),
                            'hex_data': '',
                            'base64_data': '',
                            'error': ''
                        }
                        
                        # 创建新的mode_info JSON
                        mode_info = {
                            'mode_name': '',
                            'mode_description': '',
                            'test_count': 0,
                            'test_items': []
                        }
                        
                        # 处理时间字段
                        try:
                            receive_time = datetime.fromisoformat(old_analysis_time) if old_analysis_time else datetime.now()
                        except:
                            receive_time = datetime.now()
                        
                        try:
                            created_time = datetime.fromisoformat(old_created_time) if old_created_time else datetime.now()
                        except:
                            created_time = datetime.now()
                        
                        try:
                            updated_time = datetime.fromisoformat(old_updated_time) if old_updated_time else datetime.now()
                        except:
                            updated_time = datetime.now()
                        
                        # 插入新数据
                        cursor.execute('''
                            INSERT INTO samples_new (
                                id, protocol, machine_model, machine_id,
                                sample_info, results, image, analysis_mode, mode_info,
                                raw, receive_time, status, created_time, updated_time
                            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                        ''', (
                            old_id,
                            old_protocol_type or 'CUSTOM_LIS',
                            old_instrument_model or '',
                            old_instrument_id or '',
                            json.dumps(sample_info, ensure_ascii=False),
                            json.dumps(results, ensure_ascii=False),
                            json.dumps(image, ensure_ascii=False),
                            '',  # analysis_mode
                            json.dumps(mode_info, ensure_ascii=False),
                            '',  # raw
                            receive_time.isoformat(),
                            old_status or 'PENDING',
                            created_time.isoformat(),
                            updated_time.isoformat()
                        ))
                        
                        logger.info(f"已迁移样本 {i+1}/{len(old_samples)}: {old_sample_number}")
                        
                    except Exception as e:
                        logger.error(f"迁移样本 {old_sample[0]} 失败: {e}")
                        continue
                
                # 4. 删除旧表并重命名新表
                cursor.execute('DROP TABLE IF EXISTS samples')
                cursor.execute('ALTER TABLE samples_new RENAME TO samples')
                
                conn.commit()
                logger.info("samples表迁移完成")
                return True
                
        except Exception as e:
            logger.error(f"迁移samples表失败: {e}")
            return False
    
    def migrate_database(self) -> bool:
        """执行完整的数据库迁移"""
        try:
            logger.info("开始数据库迁移...")
            
            # 1. 创建备份
            if not self.create_backup():
                logger.error("创建备份失败，停止迁移")
                return False
            
            # 2. 迁移samples表
            if not self.migrate_samples_table():
                logger.error("迁移samples表失败")
                return False
            
            logger.info("数据库迁移完成")
            return True
            
        except Exception as e:
            logger.error(f"数据库迁移失败: {e}")
            return False
    
    def rollback_migration(self) -> bool:
        """回滚迁移"""
        try:
            if not Path(self.backup_path).exists():
                logger.error("备份文件不存在，无法回滚")
                return False
            
            import shutil
            shutil.copy2(self.backup_path, self.db_path)
            logger.info("迁移已回滚")
            return True
            
        except Exception as e:
            logger.error(f"回滚迁移失败: {e}")
            return False

def main():
    """主函数"""
    import sys
    
    if len(sys.argv) != 2:
        print("用法: python database_migration.py <数据库路径>")
        sys.exit(1)
    
    db_path = sys.argv[1]
    
    if not Path(db_path).exists():
        print(f"数据库文件不存在: {db_path}")
        sys.exit(1)
    
    # 设置日志
    logging.basicConfig(
        level=logging.INFO,
        format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
    )
    
    # 执行迁移
    migrator = DatabaseMigration(db_path)
    
    print("数据库迁移工具")
    print("=" * 50)
    print(f"数据库路径: {db_path}")
    print(f"备份路径: {migrator.backup_path}")
    print()
    
    choice = input("是否执行迁移? (y/n): ").lower().strip()
    if choice != 'y':
        print("迁移已取消")
        sys.exit(0)
    
    if migrator.migrate_database():
        print("✅ 数据库迁移成功")
        print(f"备份文件: {migrator.backup_path}")
    else:
        print("❌ 数据库迁移失败")
        
        choice = input("是否回滚迁移? (y/n): ").lower().strip()
        if choice == 'y':
            if migrator.rollback_migration():
                print("✅ 迁移已回滚")
            else:
                print("❌ 回滚失败")

if __name__ == "__main__":
    main() 